/// 

/// Calls custom stored procedure.
/// 
private System.Data.DataSet CallCustomStoredProcedureForGridView() 
{
	/// Step 1: Configure parameters for your stored procedure if the stored procedure accepts parameters.
	// Each database type has different parameter type.
	// Please change them accordingly to fit your application's logic.
	// SQL Server: 
	//	Parameter type: System.Data.SqlDbType
	// Oracle:
	//	Parameter type: System.Data.OracleClient.OracleType
	// MySql:
	// 	Parameter type: MySql.Data.MySqlClient.MySqlDbType

	
	// BaseClasses.Data.StoredProcedureParameter firstParameter  = null;

	///For SQL Server: use parameter type System.Data.SqlDbType
	// firstParameter = new BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeID", 5, System.Data.SqlDbType.Int, System.Data.ParameterDirection.Input);
	
	///For Oracle: use parameter type System.Data.OracleClient.OracleType
	// firstParameter = new BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeID", 5, System.Data.OracleClient.OracleType.Int, System.Data.ParameterDirection.Input);
	
	///For MySql: use parameter type MySql.Data.MySqlClient.MySqlDbType
	// firstParameter = new BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeID", 5, MySql.Data.MySqlClient.MySqlDbType.Int, System.Data.ParameterDirection.Input);

	
	// BaseClasses.Data.StoredProcedureParameter secondParameter = null;
	
	///For SQL Server: use parameter type System.Data.SqlDbType
	// secondParameter = new BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Input);
	
	///For Oracle: use parameter type System.Data.OracleClient.OracleType	
	// secondParameter = new BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", System.Data.OracleClient.OracleType.VarChar, System.Data.ParameterDirection.Input);   
	
	///For MySql: use parameter type MySql.Data.MySqlClient.MySqlDbType
	// secondParameter = new BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", MySql.Data.MySqlClient.MySqlDbType.VarChar, System.Data.ParameterDirection.Input);    

	
	/// Step 2: Add the configured parameters to an array list.
	//BaseClasses.Data.StoredProcedureParameter[] parameterList = new BaseClasses.Data.StoredProcedureParameter[] { firstParameter, secondParameter };
	
	/// Skip steps 1 and 2 if stored procedure does not accept parameters.
	
	BaseClasses.Data.StoredProcedure myStoredProcedure = null;
	myStoredProcedure = new BaseClasses.Data.StoredProcedure("DatabaseNorthwind1", "Stored_Procedure_Name", null);

	/// Step 3: If the stored procedure accepts parameters, use the following
	// myStoredProcedure = New BaseClasses.Data.StoredProcedure("DatabaseNorthwind1", "Stored_Procedure_Name", parameterList);   

	/// Step 4: Run the stored procedure.    
	/// RunQuery() will return true if stored procedure successfully executed. Otherwise, it will return false.
	/// Use RunQuery or RunNonQuery.  RunQuery is used when a set of records is being returned.  RunNonQuery is used when one or more values are returned through output parameters.
	if (myStoredProcedure.RunQuery()) 
	{
	    /// Result from stored procedure is available 
	    /// as a DataSet or as an array of RecordValue objects.
	    return myStoredProcedure.DataSet;
	}
	else 
	{
	    ///You can raise an exception in the custom stored procedure and catch the exception and reporting it to the user.
	    ///To raise the exception:
	
	    ///SET NOCOUNT ON;
	    ///RAISERROR (N'My custom error message goes here', 11, 1)
	
	    ///IMPORTANT: If you raise an error that has a severity level of 10 or less, it is considered  
	    ///a warning, and no exception is raised. The severity of the error must be between 11 and 20
	    ///for an exception to be thrown.
	
	    ///Once the exception is raised, you can look at:
	    ///myStoredProcedure.ErrorMessage to get the text of the error message and use RegisterJScriptAlert to report this to the user.

	    return null;
	}
}

 

 
	public void LoadData() 
	{
		//  Load the GridView control when page loads.
		this.LoadGridControl();
	}

     

 
/// 

/// Binds the GridView control with data from stored procedure.
/// 
private void LoadGridControl() 
{
	//  Enable pagination for the GridView control.
	this.MyGrid.AllowPaging = true;
	this.MyGrid.DataSource = this.CallCustomStoredProcedureForGridView();
	//  Save the data obtained from stored procedure in
	//  session to be used re-bind the GridView control
	//  after postback occurs since GridView control loses 
	//  data after postback occurs.
	this.Page.Session["DataSet"] = this.MyGrid.DataSource;
	//  Let the GridView control handle rendering of columns
	//  and their names.
	this.MyGrid.AutoGenerateColumns = true;
	this.MyGrid.DataBind();
	this.MyGrid.Visible = true;
}

     

 
/// 

/// Handles pagination for the GridView control.
/// 
protected void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e) 
{
	//  Set the GridView control to display
	//  new page
	this.MyGrid.PageIndex = e.NewPageIndex;
	//  Obtain the stored procedure data
	//  that was saved in session.
	object ds = this.Page.Session["DataSet"];
	if (ds != null) 
	{
	    //  Re-bind the GridView control with the data
	    //  from session.
	    this.MyGrid.DataSource = (DataSet)ds;
	    this.MyGrid.DataBind();
	    this.MyGrid.Visible = true;
	}
	else 
	{
	    this.MyGrid.Visible = false;
	}
}